﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Collections;

/// <summary>
///ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
    public ExcelHelper()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
 

    /// <summary>
    /// 读取Excel文档
    /// </summary>
    /// <param name="Path">文件名称</param>
    /// <returns>返回一个数据集</returns>
    public static DataSet ExcelToDS(string Path)
    {
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [Sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }
        catch (System.Data.OleDb.OleDbException ex)
        {
            System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            return null;
        }
    }

    public static void DataTableToExcel(System.Data.DataView dataview, string Path, Hashtable NameMap)
    {
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            string strSql = string.Empty, strSql1 = string.Empty;
            int i, j;

            for (i = 0; i < dataview.Count; i++)
            {

                strSql = "INSERT INTO [sheet1$] (";
                strSql1 = ") values(";
                for (j = 0; j < dataview.Table.Columns.Count; j++)
                {
                    if (NameMap.ContainsKey(dataview.Table.Columns[j].ColumnName))
                    {
                        strSql += NameMap[dataview.Table.Columns[j].ColumnName] + ",";  //2414210
                        strSql1 += "'" + dataview[i][j].ToString() + "',";
                    }

                }

                try
                {
                    if (strSql.EndsWith(","))
                        strSql = strSql.Substring(0, strSql.Length - 1);
                    if (strSql1.EndsWith(","))
                        strSql1 = strSql1.Substring(0, strSql1.Length - 1);

                    strSql1 = strSql1 + ")";
                    strSql = strSql + strSql1;

                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + strSql + strSql1 + ex.Message);
                    throw new Exception(strSql + ex.Message);
                }
            }
            conn.Close();
        }
        catch (System.Data.OleDb.OleDbException ex)
        {
            System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
        }
    }

    public static void DataTableToExcel(System.Data.DataView dataview, string Path)
    {
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            string strSql = string.Empty, strSql1 = string.Empty;
            int i, j;

            for (i = 0; i < dataview.Count; i++)
            {

                strSql = "INSERT INTO [sheet1$] (";
                strSql1 = ") values(";
                for (j = 0; j < dataview.Table.Columns.Count; j++)
                {
                    strSql += dataview.Table.Columns[j].ColumnName + ",";
                    strSql1 += "'" + dataview[i][j].ToString() + "',";
                }
                //        
                try
                {
                    if (strSql.EndsWith(","))
                        strSql = strSql.Substring(0, strSql.Length - 1);
                    if (strSql1.EndsWith(","))
                        strSql1 = strSql1.Substring(0, strSql1.Length - 1);
                    strSql1 = strSql1 + ")";
                    strSql = strSql + strSql1;
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + strSql + ex.Message);
                    throw new Exception(strSql + ex.Message);
                }
            }
            conn.Close();
        }
        catch (System.Data.OleDb.OleDbException ex)
        {
            System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
        }
    }

    /// <summary>
    /// 写入Excel文档
    /// </summary>
    /// <param name="Path">文件名称</param>
    public bool SaveFP2toExcel(string Path)
    {
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
            cmd.ExecuteNonQuery();
            /*            for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
                        {
                            if(fp2.Sheets [0].Cells[i,0].Text!="")
                            {
                                cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
                                    fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
                                    "','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
                                cmd.ExecuteNonQuery ();
                            }
                        }
                        */
            conn.Close();
            return true;
        }
        catch (System.Data.OleDb.OleDbException ex)
        {
            System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
        }
        return false;
    }

}